package com.ita.os.merchant.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import com.ita.os.merchant.dao.DishDao;
import com.ita.os.merchant.po.Dish;
import com.ita.os.merchant.util.DBUtil;

public class DishDaoImpl implements DishDao {

	@Override
	public Dish findById(int id) {
		String sql = "select * from  dish where did = ? ";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		Dish d = null;
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			rs.next();
			d = new Dish();
			d.setDid(rs.getInt("did"));
			d.setDname(rs.getString("dname"));
			d.setPrice(rs.getInt("price"));
			d.setImg(rs.getString("img"));
			d.setMid(rs.getInt("mid"));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, rs);
		}
		return d;
	}

	@Override
	public int delete(int id) {
		String sql = "delete from dish where did = ? ";
		Connection con = null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			con.setAutoCommit(false);
			pst.setInt(1, id);
			m = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return m;
	}

	@Override
	public List<Dish> showAll(int mid) {
		List<Dish> ds = new ArrayList<Dish>();
		String sql = "select * from  dish where mid = ?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, mid);
			rs = pst.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("did");
				String pname = rs.getString("dname");
				int price = rs.getInt("price");
				String img = rs.getString("img");
				 mid = rs.getInt("mid");
				Dish d = new Dish(id,mid,pname, price, img );
				ds.add(d);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, rs);
		}
		return ds;
	}

	@Override
	public int insert(Dish d) {
		String sql = "insert into dish (did,dname,price,img,mid) values (my_seq.nextval,?,?,?,?)";
		Connection con = null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			con.setAutoCommit(false);
			pst.setString(1, d.getDname());
			pst.setInt(2, d.getPrice());
			pst.setString(3, d.getImg());
			pst.setInt(4, d.getMid());
			m = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return m;
	}

	@Override
	public int update(Dish d) {
		String sql = "update dish set dname = ?, price = ?,img = ?,mid =? where did = ? ";
		Connection con = null;
		PreparedStatement pst = null;
		int m = 0;
		Dish old = findById(d.getDid());
		if (old == null) {
			return m;
		}
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			con.setAutoCommit(false);
			pst.setString(1, d.getDname());
			pst.setInt(2, d.getPrice());
			pst.setString(3, d.getImg());
			pst.setInt(4, d.getMid());
			pst.setInt(5, d.getDid());
			m = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return m;
	}
	
	@Test
	public void test() throws SQLException {
		Dish d = findById(3);
		System.out.println(d.getDid());
	}

	@Test
	public void testShowAll() throws SQLException {
		List<Dish> ds = showAll(1);
		System.out.println(ds.size());
	}

	@Test
	public void testInsert() throws SQLException {
		Dish d = new Dish(Integer.valueOf(1),"Labintest2323", 234, "images/efa");
		int m = insert(d);
		System.out.println(m);
	}
	
	@Test
	public void testUpdate() throws SQLException{
		Dish d = new Dish(32,Integer.valueOf(1),"LabinUpdate", 234, "images/efa");
		int m = update(d);
		System.out.println(m);
	}


}
